﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;

namespace XCT.Class
{
    class DataProvider
    {
        private SqlConnection cnn;
        private SqlCommand cmd;
        private DataSet dts;
        private DataTable tbl;
        private SqlDataReader dr;
        private SqlDataAdapter da;
        private SqlTransaction tran;

        //Hàm khởi tạo kết nối
        public SqlConnection fBolKetNoi()
        {
            string strChuoiKetNoi = ConfigurationSettings.AppSettings.Get("ConnectionString");
            cnn = new SqlConnection();
            if (cnn.State != ConnectionState.Closed) { cnn.Close(); }
            cnn.ConnectionString = strChuoiKetNoi;
            cnn.Open();
            return cnn;
        }

        //Thực thi StoredProcedure áp dụng cho trường hợp có tham số và không có tham số
        public Boolean fBolThucThiSP(string strTenSP, SqlParameter[] arrThamSo)
        {
            int intLoop;
            try
            {
                cnn = fBolKetNoi();
                cmd = new SqlCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = strTenSP;
                cmd.CommandTimeout = 3000;
                cmd.Connection = cnn;
                if (arrThamSo != null) //Trường hợp có tham số
                {
                    for (intLoop = 0; intLoop < arrThamSo.Length; intLoop++)
                        cmd.Parameters.Add(arrThamSo[intLoop]);
                }
                cmd.ExecuteNonQuery();
                cnn.Close();
                return true;
            }
            catch
            {
                return false;
            }
            finally
            {
                if (cnn.State != ConnectionState.Closed) { cnn.Close(); }
                cnn.Dispose();
                cmd.Dispose();
            }
        }

        //Thực thi câu lệnh SQL         
        public Boolean fBolThucThiSQL(string strSQL)
        {
            try
            {
                cnn = fBolKetNoi();
                tran = cnn.BeginTransaction();
                cmd = new SqlCommand();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = strSQL;
                cmd.Connection = cnn;
                cmd.CommandTimeout = 3000;
                cmd.Transaction = tran;
                cmd.ExecuteNonQuery();
                tran.Commit();
                return true;
            }
            catch
            {
                tran.Rollback();
                return false;
            }
            finally
            {
                if (cnn.State != ConnectionState.Closed) { cnn.Close(); }
                cnn.Dispose();
                cmd.Dispose();
                tran.Dispose();
            }
        }

        //Đọc dữ liệu vào dataset bằng StoredProcedure có tham so
        public DataSet fdtsDocDuLieuSP(string strTenSP, SqlParameter[] arrThamSo)
        {
            int intLoop;
            try
            {
                cnn = fBolKetNoi();
                cmd = new SqlCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = strTenSP;
                cmd.Connection = cnn;
                cmd.CommandTimeout = 3000;
                if (arrThamSo != null) //Trường hợp có tham số
                {
                    for (intLoop = 0; intLoop < arrThamSo.Length; intLoop++)
                        cmd.Parameters.Add(arrThamSo[intLoop]);
                }
                da = new SqlDataAdapter(cmd);
                dts = new DataSet();
                da.Fill(dts);
                cnn.Close();
                return dts;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                if (cnn.State != ConnectionState.Closed) { cnn.Close(); }
                cnn.Dispose();
                cmd.Dispose();
                da.Dispose();
            }
        }

        //Đọc dữ liệu vào dataset bằng SQL
        public DataSet fdtsDocDuLieuSQL(string strSQL)
        {
            try
            {
                cnn = fBolKetNoi();
                cmd = new SqlCommand();
                cmd.CommandType = CommandType.Text;
                cmd.CommandTimeout = 3000;
                cmd.CommandText = strSQL;
                cmd.Connection = cnn;
                da = new SqlDataAdapter(cmd);
                dts = new DataSet();
                da.Fill(dts);
                cnn.Close();
                return dts;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                if (cnn.State != ConnectionState.Closed) { cnn.Close(); }
                cnn.Dispose();
                cmd.Dispose();
                da.Dispose();
            }
        }

        //Đọc dữ liệu vào datatable bằng StoredProcedure
        public DataTable ftblDocDuLieuSP(string strTenSP, SqlParameter[] arrThamSo)
        {
            int intLoop;
            try
            {
                cnn = fBolKetNoi();
                cmd = new SqlCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = strTenSP;
                cmd.Connection = cnn;
                cmd.CommandTimeout =3000;
                if (arrThamSo != null) //Trường hợp có tham số
                {
                    for (intLoop = 0; intLoop < arrThamSo.Length; intLoop++)
                        cmd.Parameters.Add(arrThamSo[intLoop]);
                }
                da = new SqlDataAdapter(cmd);
                tbl = new DataTable();
                da.Fill(tbl);
                cnn.Close();
                return tbl;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                if (cnn.State != ConnectionState.Closed) { cnn.Close(); }
                cnn.Dispose();
                cmd.Dispose();
                da.Dispose();
            }
        }

        //Đọc dữ liệu vào datatable bằng SQL
        public DataTable ftblDocDuLieuSQL(string strSQL)
        {
            try
            {
                cnn = fBolKetNoi();
                cmd = new SqlCommand();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = strSQL;
                cmd.Connection = cnn;
                cmd.CommandTimeout = 30000;
                da = new SqlDataAdapter(cmd);
                tbl = new DataTable();
                da.Fill(tbl);
                cnn.Close();
                return tbl;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                if (cnn.State != ConnectionState.Closed) { cnn.Close(); }
                cnn.Dispose();
                cmd.Dispose();
                da.Dispose();
            }
        }

        //Đọc dữ liệu vào datareader bằng StoredProcedure
        public SqlDataReader fdreadDocDuLieuSP(string strTenSP, SqlParameter[] arrThamSo)
        {
            int intLoop;
            try
            {
                cnn = fBolKetNoi();
                cmd = new SqlCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = strTenSP;
                cmd.CommandTimeout = 3000;
                cmd.Connection = cnn;
                if (arrThamSo != null) //Trường hợp có tham số
                {
                    for (intLoop = 0; intLoop < arrThamSo.Length; intLoop++)
                        cmd.Parameters.Add(arrThamSo[intLoop]);
                }
                dr = cmd.ExecuteReader();
                return dr;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        //Đọc dữ liệu vào datareader bằng SQL
        public SqlDataReader fdreadDocDuLieuSQL(string strSQL)
        {
            try
            {
                cnn = fBolKetNoi();
                cmd = new SqlCommand();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = strSQL;
                cmd.Connection = cnn;
                cmd.CommandTimeout = 3000;
                dr = cmd.ExecuteReader();
                return dr;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }
    }
}
